--
--    Description:
--       Update enabled_flag of ALR_ALERTS to Disabled and keep the old status at description field.
--
--    Revision:
--       1st    22-Nov-2005    Yau Wai Yan
--
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET SERVEROUTPUT ON SIZE 1000000;

prompt
prompt *************************************************************************************************
prompt * This script is to disable NGO specific alerts. Please specify the business group name (Cxxx).
prompt *************************************************************************************************
prompt
prompt Parameters:
accept Action prompt "Input 'Disable Cxxx Alerts' to disable NGO Specific alerts: "

DECLARE
   l_bg_name                        VARCHAR2(100);
   l_description                    VARCHAR2(1000);
   l_enabled_flag                   VARCHAR2(1);
   l_para_string                    VARCHAR2(30);

   -- data table
   CURSOR cur_data (x_bg_name varchar2) is
      SELECT data.*
      FROM   alr_alerts data
      WHERE  data.alert_name LIKE '%'||x_bg_name||'%'
      ORDER BY data.alert_id;

BEGIN

   l_para_string := UPPER('&Action');
   l_bg_name := SUBSTR(l_para_string, 9, 4);

   IF SUBSTR(l_para_string, 1, 8) = 'DISABLE ' AND SUBSTR(l_para_string, 13) = ' ALERTS' THEN
      IF l_bg_name IS NOT NULL THEN
         BEGIN
            SELECT SUBSTR(name, 4, 4)
            INTO l_bg_name
            FROM per_business_groups
            WHERE name = 'BG_'||l_bg_name;
         EXCEPTION
            WHEN OTHERS THEN
              dbms_output.put_line('Invalid Business Group Name.');
              return;
         END;

         dbms_output.put_line('bg_name = ' || l_bg_name || '.');
      END IF;
   ELSE
      dbms_output.put_line('Invalid parameter string. Program exit.');
      return;
   END IF;

   FOR sel IN cur_data (l_bg_name) LOOP
      -- clear all temporary variables
      l_description                := NULL;
      l_enabled_flag               := NULL;


      /* -- Validation and Mapping -- */
      l_description := sel.description||'@@@@@'||sel.enabled_flag;
      l_enabled_flag := 'N';


      /* -- Upload data -- */
      BEGIN
        UPDATE alr_alerts
        SET enabled_flag = l_enabled_flag,
            description = l_description
        WHERE alert_id = sel.alert_id;

        dbms_output.put_line('Update ' || sel.alert_name);
      EXCEPTION
      WHEN others THEN
         rollback;
         dbms_output.put_line('Update Alert Error. alert_id = ' || sel.alert_id);
         return;
      END;

   END LOOP;

   COMMIT;
END;
/

exit
